/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package it.trick.admin.db;

import it.trick.admin.Constants;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author takahara
 */
public class DBManager {
    
    private static Connection conn;

    /**
     * コネクションの取得
     *
     * @return Connection
     */
    public static Connection getConn() throws SQLException {
        
        if(conn != null && !conn.isClosed()) {
            return conn;
        }
        
        try {
            // 自動生成しない
            conn = DriverManager.getConnection(
                    "jdbc:derby:" + Constants.DB_PATH + ";create=false", Constants.DB_USER, Constants.DB_PASSWORD);
        } catch (SQLException se) {
            // DB の自動生成
            conn = DriverManager.getConnection(
                    "jdbc:derby:" + Constants.DB_PATH + ";create=true", Constants.DB_USER, Constants.DB_PASSWORD);

            // DB初期化
            initDB(conn);

        }
        
        return conn;
    }
    
    /**
     * DBの初期化
     */
    private static void initDB(Connection conn) throws SQLException {
        
        StringBuilder sql = new StringBuilder();
        sql.append("create table account_mst (");
        sql.append("mail_address VARCHAR(255) NOT NULL");
        sql.append(",name VARCHAR(255) NOT NULL");
        sql.append(",user_id VARCHAR(255) NOT NULL");
        sql.append(",picture_url VARCHAR(255)");
        sql.append(",access_token VARCHAR(255) NOT NULL");
        sql.append(",refresh_token VARCHAR(255) NOT NULL");
        sql.append(",constraint PK_ACCOUNT_MST primary key (mail_address)");
        sql.append(")");

        Statement smt = conn.createStatement();
        smt.execute(sql.toString());
    }
    
    public static void insertOrUpdateAccontMst(
            Connection conn, 
            String mailAddress, 
            String name,
            String userId, 
            String pictureUrl,
            String accessToken, 
            String refreshToken) throws SQLException {
        
        // 既に存在するアカウントの場合はupdateし、処理を終了
        AccountMstEntity entity = getAccountInfo(conn, mailAddress);
        if(entity != null) {
            updateAccountInfo(conn,entity);
            return;
        }
        
        StringBuilder sql = new StringBuilder();
        
        sql.append("insert into account_mst values(?,?,?,?,?,?)");

        PreparedStatement smt = conn.prepareStatement(sql.toString());
        smt.setString(1, mailAddress);
        smt.setString(2, name);
        smt.setString(3, userId);
        smt.setString(4, pictureUrl);
        smt.setString(5, accessToken);
        smt.setString(6, refreshToken);
        
        smt.executeUpdate();
    }
    
    public static AccountMstEntity getAccountInfo(Connection conn, String mailAddress) throws SQLException {
        String sql = "select * from account_mst where mail_address=?";

        PreparedStatement smt = conn.prepareStatement(sql);
        smt.setString(1, mailAddress);

        ResultSet rs = smt.executeQuery();
        AccountMstMapping mapping = new AccountMstMapping();

        while (rs.next()) {
            return mapping.createFromResultSet(rs);
        }

        return null;
    }
    
    public static List<AccountMstEntity> getAccountList(Connection conn)
            throws SQLException {
        String sql = "select * from account_mst";
        PreparedStatement smt = conn.prepareStatement(sql);

        ResultSet rs = smt.executeQuery();
        AccountMstMapping mapping = new AccountMstMapping();

        List<AccountMstEntity> accountList = new ArrayList();
        while (rs.next()) {
            accountList.add(mapping.createFromResultSet(rs));
        }

        return accountList;
    }
    
    public static void updateAccountInfo(Connection conn, AccountMstEntity entity) throws SQLException {
        String sql = "update account_mst"
                + " set"
                + "   name=?,"
                + "   user_id=?,"
                + "   picture_url=?,"
                + "   access_token=?,"
                + "   refresh_token=?"
                + " where"
                + "   mail_address=?";

        PreparedStatement smt = conn.prepareStatement(sql);
        
        smt.setString(1, entity.name);
        smt.setString(2, entity.userId);
        smt.setString(3, entity.pictureUrl);
        smt.setString(4, entity.accessToken);
        smt.setString(5, entity.refreshToken);
        smt.setString(6, entity.mailAddress);

        smt.executeUpdate();
    }
}
